import json
import random
import time
from collections import OrderedDict
from datetime import datetime, timedelta
from flask import Flask, request, jsonify, render_template
from sqlalchemy import DateTime
from sqlalchemy.exc import SQLAlchemyError

from PlateDetectionService import PlateDetectionService
import cv2
import numpy as np
from flask_cors import CORS
from flask_sqlalchemy import SQLAlchemy

from flask import send_file
from io import BytesIO
import pandas as pd

app = Flask(__name__)

# ==== 数据库配置修改 ====
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost/plate_detection?charset=utf8mb4'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'pool_size': 10,
    'pool_recycle': 300,
    'pool_pre_ping': True
}
# ==== Redis配置 ====
app.config['REDIS_HOST'] = 'localhost'
app.config['REDIS_PORT'] = 6379
app.config['REDIS_DB'] = 0
app.config['REDIS_STREAMS'] = {
    'DB_OPS': 'db_operations_stream',
    'DEAD_LETTER': 'db_dead_letter_stream'
}
app.config['PARKING_PREFIX'] = 'parking:'

db = SQLAlchemy(app)

class DetectionResult(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    plate_number = db.Column(db.String(20), nullable=False)
    # 修改字段名称并增加注释
    entry_time = db.Column('entry_time', DateTime, default=datetime.now, comment='入库时间')
    exit_time = db.Column('exit_time', DateTime, comment='出库时间')
    confidence = db.Column(db.Float(precision=4))
    amount_due = db.Column(db.Integer, default=0, comment='待支付金额（单位：元）')

    __table_args__ = (
        # 优化出库查询
        db.Index('idx_plate_exit', 'plate_number', 'exit_time'),
        # 优化所有需要按entry_time排序的查询
        db.Index('idx_entry_time', 'entry_time'),
    )

    def to_dict(self):
        return {
            "id": self.id,
            "plate_number": self.plate_number,
            "entry_time": self.entry_time.isoformat() if self.entry_time else None,
            "exit_time": self.exit_time.isoformat() if self.exit_time else None,
            "confidence": self.confidence,
            "amount_due": self.amount_due
        }


# ==== 初始化服务 ====
CORS(app, expose_headers=['Content-Disposition'])
service = PlateDetectionService("Yolov8_PlateDetect.pt")
import redis
redis_conn = redis.Redis(
    host=app.config['REDIS_HOST'],
    port=app.config['REDIS_PORT'],
    db=app.config['REDIS_DB'],
    decode_responses=True
)

# ==== 路由处理 ====
@app.route('/')
def index():
    return render_template('index.html')


# ===== 入库接口改造 =====
@app.route('/detect/in', methods=['POST'])
def vehicle_in():
    """处理车辆入库（Redis优先+异步持久化）"""
    if 'file' not in request.files:
        return jsonify({"error": "No file part"}), 400

    file = request.files['file']
    if file.filename == '':
        return jsonify({"error": "No selected file"}), 400

    try:
        # 图片处理逻辑保持不变
        file_data = file.read()
        nparr = np.frombuffer(file_data, np.uint8)
        image = cv2.imdecode(nparr, cv2.IMREAD_COLOR)
        if image is None:
            return jsonify({"error": "Invalid image file"}), 400

        results_list = service.process_image(image)
        if not results_list:
            return jsonify({"error": "未识别到有效车牌"}), 400

        success_data = []
        errors = []

        for result in results_list:
            plate_number = result[0]
            confidence = result[1]

            try:
                redis_key = f"{app.config['PARKING_PREFIX']}{plate_number}"
                with redis_conn.pipeline() as pipe:
                    max_retries = 3  # 最大重试次数
                    retry_count = 0
                    success = False  # 操作成功标志
                    conflict_error = False  # 冲突错误标志

                    while retry_count < max_retries:
                        try:
                            pipe.watch(redis_key)  # 监控键变化

                            # 检查是否已存在记录（原子化判断）
                            if pipe.exists(redis_key):
                                errors.append(f"{plate_number}: 已存在未出库记录")
                                pipe.unwatch()  # 显式取消监控
                                conflict_error = True  # 标记为冲突错误
                                break  # 直接退出循环

                            # --- 无冲突时执行事务 ---
                            entry_time = datetime.now()
                            # 添加随机TTL偏移量（±4小时范围内）
                            base_ttl = 72 * 3600  # 72小时基础TTL
                            random_offset = random.randint(-4 * 3600, 4 * 3600)  # 随机偏移量（-4小时到+4小时）
                            final_ttl = max(base_ttl + random_offset, 3600)  # 确保至少1小时TTL

                            # 定义事务操作
                            pipe.multi()
                            pipe.hset(
                                redis_key,
                                mapping={
                                    "entry_time": entry_time.timestamp(),
                                    "confidence": confidence
                                }
                            )
                            pipe.expire(redis_key, final_ttl)  # 使用带随机偏移的TTL
                            pipe.execute()  # 提交事务

                            # 标记成功并退出循环
                            success = True
                            break

                        except redis.WatchError:
                            # 捕获并发冲突，进行重试
                            retry_count += 1
                            app.logger.warning(f"{plate_number}: 并发冲突，正在重试 ({retry_count}/{max_retries})")
                            continue  # 继续下一次循环

                    # --- 后置检查 ---
                    if conflict_error:
                        # 处理已存在记录的错误（无需重试）
                        raise redis.RedisError("记录已存在")  # 触发外层 except 块

                    if not success:
                        # 重试耗尽失败
                        errors.append(f"{plate_number}: 入库操作冲突超过最大重试次数 {max_retries}")
                        raise redis.RedisError("重试上限")

                    # --- 事务成功后的处理 ---
                    success_data.append({
                        "plate_number": plate_number,
                        "confidence": float(confidence),
                        "entry_time": entry_time.isoformat(),
                        "plate_image": result[2] if len(result) > 2 else None
                    })

                    # 异步数据库操作（确保仅在成功时执行）
                    send_db_operation('insert', {
                        'plate_number': plate_number,
                        'entry_time': entry_time.timestamp(),
                        'confidence': confidence
                    })

            except redis.RedisError as e:
                # 统一错误处理（包含 conflict_error 和重试耗尽）
                errors.append(f"{plate_number}: 缓存操作失败 - {str(e)}")
                app.logger.error(f"Redis入库异常: {str(e)}")

        return jsonify({
            "success": success_data,
            "errors": errors
        }), 200 if success_data else 400

    except Exception as e:
        app.logger.error(f"全局处理失败: {str(e)}")
        return jsonify({"error": "文件处理失败", "detail": str(e)}), 500


# ===== 出库接口改造 =====
@app.route('/detect/out', methods=['POST'])
def vehicle_out():
    """处理车辆出库（Redis优先+异步更新）"""
    if 'file' not in request.files:
        return jsonify({"error": "No file part"}), 400

    try:
        file = request.files['file']
        results_list = service.process_image(cv2.imdecode(np.frombuffer(file.read(), np.uint8), cv2.IMREAD_COLOR))
        if not results_list:
            return jsonify({"error": "未识别到车牌"}), 400

        response_data = []
        errors = []

        for result in results_list:
            plate_number = result[0]
            try:
                redis_key = f"{app.config['PARKING_PREFIX']}{plate_number}"

                # ---- 初始化 entry_time，优先从 Redis 获取 ----
                entry_time = None
                # 1. 先查 Redis
                redis_entry_time = redis_conn.hget(redis_key, "entry_time")
                if redis_entry_time:
                    entry_time = float(redis_entry_time)
                else:
                    # 2. Redis 无记录时查数据库
                    try:
                        # 查询最近一条未出库的记录（exit_time 为空）
                        db_record = DetectionResult.query.filter(
                            DetectionResult.plate_number == plate_number,
                            DetectionResult.exit_time.is_(None)
                        ).order_by(DetectionResult.entry_time.desc()).first()

                        if db_record:
                            entry_time = db_record.entry_time.timestamp()  # 转为时间戳
                            app.logger.info(f"从数据库恢复入库记录: {plate_number} entry_time={entry_time}")
                        else:
                            # Redis 和数据库均无有效记录
                            errors.append(f"{plate_number}: 无有效入库记录")
                            raise ValueError("停车记录不存在")
                    except SQLAlchemyError as db_error:
                        errors.append(f"{plate_number}: 数据库查询失败")
                        app.logger.error(f"数据库查询异常: {str(db_error)}")
                        raise redis.RedisError("数据库操作异常")

                # ---- 进入 Redis 事务处理 ----
                with redis_conn.pipeline() as pipe:
                    max_retries = 3
                    retry_count = 0
                    success = False

                    while retry_count < max_retries:
                        try:
                            pipe.watch(redis_key)  # 乐观锁监控

                            # 计算停车费用
                            exit_time = datetime.now().timestamp()
                            duration = exit_time - entry_time
                            charge_units = int(duration // 1800)  # 30分钟计费单位
                            amount_due = charge_units * 1  # 单价1元

                            # 原子化操作
                            pipe.multi()
                            pipe.delete(redis_key)  # 删除 Redis 记录
                            pipe.execute()

                            success = True
                            break  # 成功退出循环

                        except redis.WatchError:
                            # 并发冲突重试
                            retry_count += 1
                            app.logger.warning(f"{plate_number}: 操作冲突，重试 ({retry_count}/{max_retries})")
                            continue

                    # ---- 后置检查 ----
                    if not success:
                        error_msg = f"{plate_number}: 操作冲突超过最大重试次数 {max_retries}"
                        errors.append(error_msg)
                        raise redis.RedisError(error_msg)

                    # ---- 成功处理逻辑 ----
                    # 计算停车时长展示
                    total_seconds = int(duration)
                    hours, remainder = divmod(total_seconds, 3600)
                    minutes = remainder // 60

                    response_data.append({
                        "plate_number": plate_number,
                        "parking_duration": f"{hours}小时{minutes}分钟",
                        "amount_due": amount_due,
                        "exit_time": datetime.fromtimestamp(exit_time).isoformat()
                    })

                    # 异步更新数据库（确保最终一致性）
                    send_db_operation('update', {
                        'plate_number': plate_number,
                        'entry_time': entry_time,
                        'exit_time': exit_time,
                        'amount_due': amount_due
                    })

            except (redis.RedisError, ValueError, SQLAlchemyError) as e:
                errors.append(f"{plate_number}: 处理失败 - {str(e)}")
                app.logger.error(f"出库流程异常: {str(e)}")

        return jsonify({
            "success": response_data,
            "errors": errors
        }), 200 if response_data else 400

    except Exception as e:
        app.logger.error(f"全局处理失败: {str(e)}")
        return jsonify({"error": "文件处理失败"}), 500


# ===== 实时状态接口改造 =====
@app.route('/stats/current', methods=['GET'])
def current_status():
    """获取实时状态（Redis优化版）"""
    try:
        # 使用SCAN代替KEYS命令防止阻塞
        cursor = 0  # 修改为整数类型
        in_count = 0
        while True:
            cursor, keys = redis_conn.scan(
                cursor=cursor,
                match=f"{app.config['PARKING_PREFIX']}*",
                count=100  # 推荐添加count参数优化扫描
            )
            in_count += len(keys)
            if cursor == 0:  # 当返回的cursor为0时终止循环
                break

        return jsonify({
            "code": 200,
            "data": {
                "in_count": in_count,
                "total_spaces": 200,
                "available_spaces": 200 - in_count
            }
        }), 200
    except redis.RedisError as e:
        app.logger.error(f"Redis连接异常: {str(e)}")
        # 降级方案：回退数据库查询
        in_count = DetectionResult.query.filter(
            DetectionResult.exit_time.is_(None)
        ).count()
        return jsonify({
            "code": 200,
            "data": {
                "in_count": in_count,
                "total_spaces": 200,
                "available_spaces": 200 - in_count
            }
        }), 200
    except Exception as e:
        app.logger.error(f"状态查询失败: {str(e)}")
        return jsonify({"code": 500, "error": "服务器内部错误"}), 500

# ===== 发送到消息队列操作方法 =====
# def send_db_operation(task_type, payload):
#     """发送数据库操作到消息队列"""
#     try:
#         message = {
#             'task_type': task_type,
#             'payload': json.dumps(payload),
#             'retries': 0,
#             'timestamp': time.time()
#         }
#         redis_conn.xadd(
#             name=app.config['REDIS_STREAMS']['DB_OPS'],
#             fields=message,
#             maxlen=1000  # 保留最新1000条消息
#         )
#     except Exception as e:
#         app.logger.error(f"消息发送失败: {str(e)}")
def send_db_operation(task_type, payload):
    try:
        # 生成业务唯一标识（存入消息内容）
        business_id = f"{payload['plate_number']}:{payload['entry_time']}:{task_type}"

        message = {
            'task_type': task_type,
            'payload': json.dumps(payload),
            'business_id': business_id,  # ⭐️业务ID存入字段
            'retries': 0,
            'timestamp': time.time()
        }

        # 让Redis自动生成消息ID
        redis_conn.xadd(
            name=app.config['REDIS_STREAMS']['DB_OPS'],
            fields=message,
            id='*',  # ⭐️使用自动生成ID
            maxlen=1000
        )
    except Exception as e:
        app.logger.error(f"消息发送失败: {str(e)}")



@app.route('/detect/records', methods=['GET'])
def query_records():
    """查询停车记录接口
    参数：
    plate_number - 车牌号（可选）
    start_entry_time - 入库开始时间（ISO格式，可选）
    end_entry_time - 入库结束时间（ISO格式，可选）
    start_exit_time - 出库开始时间（ISO格式，可选）
    end_exit_time - 出库结束时间（ISO格式，可选）
    has_exit - 是否已出库（true/false，可选）
    page - 页码（默认1）
    per_page - 每页数量（默认10）
    """
    try:
        # 构建基础查询
        query = DetectionResult.query

        # 处理车牌号过滤
        if plate := request.args.get('plate_number'):
            query = query.filter(DetectionResult.plate_number == plate)

        # 处理入库时间范围
        if start_entry := request.args.get('start_entry_time'):
            start_entry = datetime.fromisoformat(start_entry)
            query = query.filter(DetectionResult.entry_time >= start_entry)
        if end_entry := request.args.get('end_entry_time'):
            end_entry = datetime.fromisoformat(end_entry)
            query = query.filter(DetectionResult.entry_time <= end_entry)

        # 处理出库时间范围
        if start_exit := request.args.get('start_exit_time'):
            start_exit = datetime.fromisoformat(start_exit)
            query = query.filter(DetectionResult.exit_time >= start_exit)
        if end_exit := request.args.get('end_exit_time'):
            end_exit = datetime.fromisoformat(end_exit)
            query = query.filter(DetectionResult.exit_time <= end_exit)

        # 处理是否已出库
        if has_exit := request.args.get('has_exit'):
            if has_exit.lower() == 'true':
                query = query.filter(DetectionResult.exit_time.isnot(None))
            else:
                query = query.filter(DetectionResult.exit_time.is_(None))

        # 分页处理
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 10, type=int)
        pagination = query.order_by(DetectionResult.entry_time.desc()).paginate(
            page=page,
            per_page=per_page,
            error_out=False
        )

        # 构造响应数据
        return jsonify({
            "data": [record.to_dict() for record in pagination.items],
            "pagination": {
                "total": pagination.total,
                "pages": pagination.pages,
                "current_page": pagination.page,
                "per_page": pagination.per_page
            }
        }), 200

    except ValueError as e:
        app.logger.error(f"参数格式错误: {str(e)}")
        return jsonify({"error": "时间参数格式不正确（需ISO格式）"}), 400
    except Exception as e:
        app.logger.error(f"查询失败: {str(e)}")
        return jsonify({"error": "查询失败"}), 500

@app.route('/stats/hourly', methods=['GET'])
def hourly_stats():
    """按两小时时段统计车辆入库数量
    参数：
    start_time - 统计开始时间（ISO格式，默认30天前）
    end_time - 统计结束时间（ISO格式，默认当前时间）
    """
    try:
        # 处理时间参数
        start_time = request.args.get('start_time',
                    (datetime.now() - timedelta(days=30)).isoformat())
        end_time = request.args.get('end_time',
                    datetime.now().isoformat())

        start = datetime.fromisoformat(start_time)
        end = datetime.fromisoformat(end_time)

        # 查询数据库记录
        records = DetectionResult.query.filter(
            DetectionResult.entry_time >= start,
            DetectionResult.entry_time <= end
        ).all()

        # 初始化时间段字典（保持顺序）
        time_slots = OrderedDict()
        for hour in range(0, 24, 2):
            end_hour = (hour + 2) % 24
            slot_key = f"{hour:02d}:00-{end_hour:02d}:00"
            time_slots[slot_key] = 0

        # 统计时段数据
        for record in records:
            hour = record.entry_time.hour
            slot_index = (hour // 2) * 2
            end_hour = (slot_index + 2) % 24
            slot_key = f"{slot_index:02d}:00-{end_hour:02d}:00"
            time_slots[slot_key] += 1

        # 转换格式并保持时间段顺序
        result = [{"time_slot": k, "count": v}
                for k, v in time_slots.items()]

        return jsonify({
            "code": 200,
            "data": result
        }), 200

    except ValueError:
        return jsonify({"code": 400, "error": "时间格式不正确（需ISO格式）"}), 400
    except Exception as e:
        app.logger.error(f"统计失败: {str(e)}")
        return jsonify({"code": 500, "error": "服务器内部错误"}), 500

@app.route('/records/export', methods=['GET'])
def export_records():
    """导出停车记录Excel接口
    参数规则与/detect/records接口保持一致
    """
    try:
        # ==== 参数处理 ====
        query = DetectionResult.query

        # 车牌号过滤
        if plate := request.args.get('plate_number'):
            query = query.filter(DetectionResult.plate_number == plate)

        # 入库时间范围
        if start_entry := request.args.get('start_entry_time'):
            query = query.filter(DetectionResult.entry_time >= datetime.fromisoformat(start_entry))
        if end_entry := request.args.get('end_entry_time'):
            query = query.filter(DetectionResult.entry_time <= datetime.fromisoformat(end_entry))

        # 出库状态
        if has_exit := request.args.get('has_exit'):
            if has_exit.lower() == 'true':
                query = query.filter(DetectionResult.exit_time.isnot(None))
            else:
                query = query.filter(DetectionResult.exit_time.is_(None))

        # ==== 数据查询 ====
        records = query.order_by(DetectionResult.entry_time.desc()).all()

        # ==== 数据格式化 ====
        df = pd.DataFrame([r.to_dict() for r in records])

        if not df.empty:
            # 时间格式化
            df['entry_time'] = pd.to_datetime(df['entry_time']).dt.strftime('%Y-%m-%d %H:%M:%S')
            df['exit_time'] = df['exit_time'].apply(
                lambda x: pd.to_datetime(x).strftime('%Y-%m-%d %H:%M:%S') if x else "未出库")

            # 数值格式化
            df['confidence'] = df['confidence'].apply(lambda x: f"{x * 100:.1f}%")
            df['amount_due'] = df['amount_due'].apply(lambda x: f"{x:.2f}元")

            # 列名翻译
            df = df.rename(columns={
                "plate_number": "车牌号",
                "entry_time": "入库时间",
                "exit_time": "出库时间",
                "confidence": "置信度",
                "amount_due": "应付金额"
            })

        # ==== Excel生成 ====
        output = BytesIO()
        with pd.ExcelWriter(
                output,
                engine='openpyxl',
                mode='w',
                datetime_format='YYYY-MM-DD HH:mm:ss'
        ) as writer:
            df.to_excel(writer, index=False, sheet_name='停车记录')

            # 自动调整列宽
            worksheet = writer.sheets['停车记录']
            for idx, col in enumerate(df.columns, 1):
                column = worksheet.column_dimensions[chr(64 + idx)]
                max_length = max(
                    df[col].astype(str).map(len).max(),
                    len(col)
                ) + 2
                column.width = max_length

        output.seek(0)  # 重置指针

        # ==== 响应设置 ====
        response = send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name="export.xlsx"  # 固定文件名
        )

        # 添加自定义响应头
        response.headers['Access-Control-Expose-Headers'] = 'Content-Disposition'
        response.headers['Cache-Control'] = 'no-store, max-age=0'

        return response

    except ValueError as e:
        app.logger.error(f"参数错误: {str(e)}")
        return jsonify({"error": "时间参数格式不正确"}), 400
    except Exception as e:
        app.logger.error(f"导出失败: {str(e)}", exc_info=True)
        return jsonify({"error": "文件生成失败"}), 500

@app.route('/stats/heatmap', methods=['GET'])
def heatmap_stats():
    """入库车流热力图数据"""
    try:
        range_map = {'week':7, 'month':30, 'year':365}
        days = range_map.get(request.args.get('range', 'week'), 7)
        start_time = datetime.now() - timedelta(days=days)

        # 查询部分
        query = db.session.query(
            db.func.weekday(DetectionResult.entry_time).label('weekday'),
            (db.func.floor(db.func.hour(DetectionResult.entry_time) / 2) * 2).label('hour_slot'),
            db.func.count().label('count')
        ).filter(
            DetectionResult.entry_time >= start_time,
            DetectionResult.entry_time <= datetime.now()
        ).group_by('weekday', 'hour_slot')

        raw_data = query.all()

        # 调整转换逻辑（MySQL WEEKDAY返回0=周一，需要转为1=周一）
        result = []
        for weekday, hour_slot, count in raw_data:
            result.append({
                "weekday": int(weekday) + 1,  # 0→1（周一）, 6→7（周日）
                "time_slot": f"{int(hour_slot):02d}:00-{(int(hour_slot)+2)%24:02d}:00",
                "count": count
            })

        return jsonify(result), 200

    except Exception as e:
        app.logger.error(f"热力图数据获取失败: {str(e)}")
        return jsonify({"error": "数据获取失败"}), 500

@app.route('/stats/revenue', methods=['GET'])
def revenue_analysis():
    """营收统计分析
    参数：
    start_time - 开始时间（ISO格式）
    end_time - 结束时间（ISO格式）
    granularity - 统计粒度（day/week/month，默认day）
    """
    try:
        # 参数处理
        start = datetime.fromisoformat(request.args.get('start_time', (datetime.now() - timedelta(days=7)).isoformat()))
        end = datetime.fromisoformat(request.args.get('end_time', datetime.now().isoformat()))
        granularity = request.args.get('granularity', 'day')

        # SQL时间格式化字符串
        format_map = {
            'day': '%Y-%m-%d',
            'week': '%Y-%u',    # ISO周数
            'month': '%Y-%m'
        }
        time_format = format_map.get(granularity, '%Y-%m-%d')

        # 执行分组查询
        results = db.session.query(
            db.func.date_format(DetectionResult.exit_time, time_format).label('period'),
            db.func.sum(DetectionResult.amount_due).label('total'),
            db.func.count().label('orders')
        ).filter(
            DetectionResult.exit_time.between(start, end),
            DetectionResult.amount_due > 0
        ).group_by('period').order_by('period').all()

        # 构造响应数据
        return jsonify([{
            "period": r.period,
            "total_revenue": float(r.total),
            "order_count": r.orders
        } for r in results]), 200

    except Exception as e:
        app.logger.error(f"营收统计失败: {str(e)}")
        return jsonify({"error": "统计失败"}), 500


if __name__ == '__main__':
    with app.app_context():
        try:
            # 检查表是否存在
            inspector = db.inspect(db.engine)
            if not inspector.has_table("detection_result"):
                print("正在创建数据库表...")
                db.create_all()
                print(f"已创建表: {inspector.get_table_names()}")
            else:
                print("表已存在")
        except Exception as e:
            print(f"数据库初始化失败: {str(e)}")
            exit(1)

    app.run(debug=False)

